﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Collections;
using System.Reflection;
using System.Collections.Specialized;
using System.Configuration;
using System.ComponentModel;
using System.IO;
using System.Text.RegularExpressions;
using CP.Utility;

namespace CP.DBService
{
    public class DBHelper
    {
        protected string ConnectionString { get; set; }
        protected DataBaseType DBType { get; set; }
        private static DBHelper helper;
        private static bool hasRead = false;
        private static DBHelper gpmHelper;
        protected DBHelper() { }
        /// <summary>
        /// 创建单例模式
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static DBHelper CreateInstance()
        {
            if (helper == null)
            {
                helper = new DBHelper();
                helper.ConnectionString = EncryptHelper.DecryptDES(ConfigurationManager.ConnectionStrings["BizConnectionString"].ConnectionString, "masicsyc");
                //helper.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                helper.DBType = DataBaseType.Oracle;
            }
            return helper;
        }

        public static DBHelper GPMCreateInstance()
        {
            if (gpmHelper == null)
            {
                gpmHelper = new DBHelper();
                gpmHelper.ConnectionString = EncryptHelper.DecryptDES(ConfigurationManager.ConnectionStrings["GPMConnectionString"].ConnectionString, "masicgpm");
                //helper.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                gpmHelper.DBType = DataBaseType.Oracle;
            }
            return gpmHelper;
        }

        public static DBHelper CreateNonSingleTonInstance()
        {
            helper = new DBHelper();
            helper.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            helper.DBType = DataBaseType.Oracle;
            return helper;

        }

        DbTransaction transaction = null;
        public DbTransaction Transaction
        {
            get { return transaction; }
            set { transaction = value; }
        }
        public void Commit()
        {
            Transaction.Commit();
            Transaction = null;
            GC.Collect();
        }
        public void Rollback()
        {
            Transaction.Rollback();
            Transaction = null;
            GC.Collect();
        }
        public virtual void BeginTransaction()
        {
            DbConnection transConnection = CreateConnection();
            DbTransaction transaction = transConnection.BeginTransaction();
            Transaction = transaction;
        }
        public virtual void CloseConnection(DbConnection connection)
        {
            if (Transaction == null)
                connection.Close();
        }
        /// <summary>
        /// 创建连接
        /// </summary>
        /// <returns></returns>
        protected DbConnection CreateConnection()
        {
            if (Transaction != null) return Transaction.Connection;
            DbConnection connection = null;
            switch (DBType)
            {
                case DataBaseType.Access:
                    connection = new System.Data.OleDb.OleDbConnection(ConnectionString);
                    break;
                case DataBaseType.MySql:
                    connection = new System.Data.OleDb.OleDbConnection(ConnectionString);
                    break;
                case DataBaseType.Oracle:
                    connection = new System.Data.OracleClient.OracleConnection(ConnectionString);
                    break;
                case DataBaseType.SqlServer:
                    connection = new System.Data.SqlClient.SqlConnection(ConnectionString);
                    break;
            }
            connection.Open();
            return connection;
        }
        public DbCommand CreateCommand()
        {
            DbCommand command = CreateConnection().CreateCommand();
            command.Transaction = Transaction;
            return command;
        }
        /// <summary>
        /// 创建命令
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="sqlSegment"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected DbCommand CreateCommand(DbConnection connection, string sqlSegment, IDataParameter[] parameters, CommandType commandType)
        {
            DbCommand command = connection.CreateCommand();
            command.CommandText = sqlSegment;
            command.CommandType = commandType;
            if (parameters != null)
                command.Parameters.AddRange(parameters);
            command.Transaction = Transaction;
            return command;
        }
        /// <summary>
        /// 创建适配器
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="sqlSegment"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected DbDataAdapter CreateAdapter(DbConnection connection, string sqlSegment, IDataParameter[] parameters, CommandType commandType)
        {
            DbCommand command = CreateCommand(connection, sqlSegment, parameters, commandType);
            command.CommandText = sqlSegment;
            //command.CommandType = commandType;
            //if (parameters != null)
            //    command.Parameters.AddRange(parameters);

            DbDataAdapter adapter = null;
            switch (DBType)
            {
                case DataBaseType.Access:
                    adapter = new System.Data.OleDb.OleDbDataAdapter((System.Data.OleDb.OleDbCommand)command);
                    break;
                case DataBaseType.MySql:
                    adapter = new System.Data.OleDb.OleDbDataAdapter((System.Data.OleDb.OleDbCommand)command);
                    break;
                case DataBaseType.Oracle:
                    adapter = new System.Data.OracleClient.OracleDataAdapter((System.Data.OracleClient.OracleCommand)command);
                    break;
                case DataBaseType.SqlServer:
                    adapter = new System.Data.SqlClient.SqlDataAdapter((System.Data.SqlClient.SqlCommand)command);
                    break;
            }
            return adapter;
        }

        public virtual T ExecuteScalar<T>(string sqlSegment)
        {
            return ExecuteScalar<T>(sqlSegment, null);
        }
        public virtual T ExecuteScalar<T>(string sqlSegment, IDataParameter[] parameters)
        {
            return ExecuteScalar<T>(sqlSegment, null, CommandType.Text);
        }
        public virtual T ExecuteScalar<T>(string sqlSegment, CommandType commandType)
        {
            return ExecuteScalar<T>(sqlSegment, null, commandType);
        }
        public virtual T ExecuteScalar<T>(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
        {
            T result = default(T);
            object value = ExecuteScalar(sqlSegment, parameters, commandType);

            if (value != null && value != DBNull.Value)
            {
                result = (T)TypeDescriptor.GetConverter(typeof(T)).ConvertFrom(value.ToString());
            }
            return result;
        }

        public virtual object ExecuteScalar(string sqlSegment)
        {
            return ExecuteScalar(sqlSegment, null);
        }
        public virtual object ExecuteScalar(string sqlSegment, IDataParameter[] parameters)
        {
            return ExecuteScalar(sqlSegment, null, CommandType.Text);
        }
        public virtual object ExecuteScalar(string sqlSegment, CommandType commandType)
        {
            return ExecuteScalar(sqlSegment, null, commandType);
        }
        public virtual object ExecuteScalar(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
        {
            DbConnection connection = CreateConnection();
            DbCommand command = CreateCommand(connection, sqlSegment, parameters, commandType);

            object result = command.ExecuteScalar();
            command.Parameters.Clear();
            CloseConnection(connection);
            return result;
        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数，用于增/删/改数据
        /// </summary>
        /// <param name="sqlSegment"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual int ExecuteNonQuery(string sqlSegment)
        {
            return ExecuteNonQuery(sqlSegment, null, CommandType.Text);
        }
        public virtual int ExecuteNonQuery(string sqlSegment, IDataParameter[] parameters)
        {
            return ExecuteNonQuery(sqlSegment, null, CommandType.Text);
        }
        public virtual int ExecuteNonQuery(string sqlSegment, CommandType commandType)
        {
            return ExecuteNonQuery(sqlSegment, null, commandType);
        }
        public virtual int ExecuteNonQuery(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
        {
            DbConnection connection = CreateConnection();
            DbCommand command = CreateCommand(connection, sqlSegment, parameters, commandType);
            int result = command.ExecuteNonQuery();
            command.Parameters.Clear();
            CloseConnection(connection);
            return result;
        }

        /// <summary>
        /// 执行SQL语句，返回一张表，用于查询数据
        /// </summary>
        /// <param name="sqlSegment"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual DataTable ExecuteDataTable(string sqlSegment)
        {
            return ExecuteDataTable(sqlSegment, null);
        }
        public virtual DataTable ExecuteDataTable(string sqlSegment, IDataParameter[] parameters)
        {
            return ExecuteDataTable(sqlSegment, null, CommandType.Text);
        }
        public virtual DataTable ExecuteDataTable(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
        {

            DataTable dataTable = new DataTable();
            DbConnection connection = CreateConnection();
            DbDataAdapter adapter = CreateAdapter(connection, sqlSegment, parameters, commandType);
            adapter.Fill(dataTable);
            adapter.SelectCommand.Parameters.Clear();
            CloseConnection(connection);
            return dataTable;

        }

        /// <summary>
        /// 执行SQL语句，返回一个Reader对象，用于查询数据
        /// </summary>
        /// <param name="sqlSegment"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        //public virtual DbDataReader ExecuteReader(string sqlSegment)
        //{
        //    return ExecuteReader(sqlSegment, null);
        //}
        //public virtual DbDataReader ExecuteReader(string sqlSegment, IDataParameter[] parameters)
        //{
        //    return ExecuteReader(sqlSegment, null,CommandType.Text);
        //}
        //public virtual DbDataReader ExecuteReader(string sqlSegment, IDataParameter[] parameters,CommandType commandType)
        //{
        //    DbConnection connection = CreateConnection();
        //    DbCommand command = CreateCommand(connection, sqlSegment, parameters,commandType);
        //    DbDataReader reader = command.ExecuteReader();
        //    return reader;
        //}

        public virtual IList<T> ExecuteObject<T>(string sqlSegment)
        {
            return ExecuteObject<T>(sqlSegment, null);
        }
        public virtual IList<T> ExecuteObject<T>(string sqlSegment, IDataParameter[] parameters)
        {
            return ExecuteObject<T>(sqlSegment, parameters, CommandType.Text);
        }
        public virtual IList<T> ExecuteObject<T>(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
        {
            return ExecuteObject<T>(sqlSegment, parameters, commandType, null);
        }
        public virtual IList<T> ExecuteObject<T>(string sqlSegment, IDataParameter[] parameters, CommandType commandType, string entity_type)
        {
            DbConnection connection = CreateConnection();
            DbCommand command = CreateCommand(connection, sqlSegment, parameters, commandType);

            IList<T> entities = new List<T>();
            try
            {
                using (DbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        T entity = Activator.CreateInstance<T>();
                        if (!string.IsNullOrEmpty(entity_type))
                        {
                            entity = (T)PortalService.CreateEntity(entity_type);
                        }

                        var properties = entity.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
                        foreach (var property in properties)
                        {
                            object value = reader[property.Name];
                            object new_value = null;
                            if (value == null || value == DBNull.Value)
                            {
                                if (property.PropertyType == typeof(int) ||
                                    property.PropertyType == typeof(Int32) ||
                                    property.PropertyType == typeof(decimal) ||
                                    property.PropertyType == typeof(float) ||
                                    property.PropertyType == typeof(double))
                                {
                                    new_value = 0;
                                }
                                else if (property.PropertyType == typeof(bool))
                                    new_value = false;
                                else if (property.PropertyType == typeof(DateTime))
                                    new_value = DateTime.MinValue;
                            }
                            else
                            {
                                if (property.PropertyType == value.GetType())
                                    new_value = value;
                                else if (property.PropertyType == typeof(DateTime?))
                                    new_value = value;
                                else if (property.PropertyType == typeof(bool))
                                    new_value = value.ToString() == "0" ? false : true;
                                else if (property.PropertyType == typeof(byte[]))
                                    new_value = (byte[])value;
                                else
                                    new_value = TypeDescriptor.GetConverter(property.PropertyType).ConvertFrom(value.ToString());

                                //if (new_value != null)
                                //{
                                //    //if (new_value.GetType() == typeof(string) && new_value.ToString() == " ")
                                //    //    new_value = "";
                                //    if (new_value.GetType() == typeof(decimal) && (decimal)new_value != 0m && new_value.ToString().IndexOf(".") > 0)
                                //    {
                                //        string str_new_value = new_value.ToString();
                                //        str_new_value = Regex.Replace(str_new_value, "0+$", "");
                                //        new_value = decimal.Parse(str_new_value);
                                //    }
                                //}
                            }

                            property.SetValue(entity, new_value, null);
                        }
                        entities.Add(entity);
                    }
                    reader.Close();

                }
            }
            catch (Exception ex)
            {
                throw new Exception("执行SQL语句发生错误:" + sqlSegment + ".详细描述" + ex.Message, ex);
            }
            finally
            {
                CloseConnection(connection);
            }
            command.Parameters.Clear();
            return entities;

        }

        public virtual T ExecuteSingleObject<T>(string sqlSegment)
        {
            return ExecuteSingleObject<T>(sqlSegment, null);
        }
        public virtual T ExecuteSingleObject<T>(string sqlSegment, IDataParameter[] parameters)
        {
            return ExecuteSingleObject<T>(sqlSegment, parameters, CommandType.Text);
        }
        public virtual T ExecuteSingleObject<T>(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
        {
            return ExecuteSingleObject<T>(sqlSegment, parameters, commandType, null);
        }
        public virtual T ExecuteSingleObject<T>(string sqlSegment, IDataParameter[] parameters, CommandType commandType, string entity_type)
        {
            DbConnection connection = CreateConnection();
            DbCommand command = CreateCommand(connection, sqlSegment, parameters, commandType);

            T entity = Activator.CreateInstance<T>();
            try
            {
                using (DbDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        if (!string.IsNullOrEmpty(entity_type))
                        {
                            entity = (T)PortalService.CreateEntity(entity_type);
                        }

                        var properties = entity.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
                        foreach (var property in properties)
                        {
                            object value = reader[property.Name];
                            object new_value = null;
                            if (value == DBNull.Value)
                            {
                                if (property.PropertyType == typeof(int) ||
                                    property.PropertyType == typeof(Int32) ||
                                    property.PropertyType == typeof(decimal) ||
                                    property.PropertyType == typeof(float) ||
                                    property.PropertyType == typeof(double))
                                {
                                    new_value = 0;
                                }
                                else if (property.PropertyType == typeof(bool))
                                    new_value = false;
                                else if (property.PropertyType == typeof(DateTime))
                                    new_value = DateTime.MinValue;
                            }
                            else
                            {
                                if (property.PropertyType == value.GetType())
                                    new_value = value;
                                else if (property.PropertyType == typeof(DateTime?))
                                    new_value = value;
                                else if (property.PropertyType == typeof(bool))
                                    new_value = value.ToString() == "0" ? false : true;
                                else if (property.PropertyType == typeof(byte[]))
                                    new_value = (byte[])value;
                                else
                                    new_value = TypeDescriptor.GetConverter(property.PropertyType).ConvertFrom(value.ToString());
                            }

                            property.SetValue(entity, new_value, null);
                        }
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception("执行SQL语句发生错误:" + sqlSegment + ".详细描述" + ex.Message, ex);
            }
            finally
            {
                CloseConnection(connection);
            }
            command.Parameters.Clear();
            return entity;
        }

        public virtual IList<T> ExecuteSimpleTypeList<T>(string sqlSegment)
        {
            return ExecuteSimpleTypeList<T>(sqlSegment, null);
        }
        public virtual IList<T> ExecuteSimpleTypeList<T>(string sqlSegment, IDataParameter[] parameters)
        {
            return ExecuteSimpleTypeList<T>(sqlSegment, parameters, CommandType.Text);
        }
        public virtual IList<T> ExecuteSimpleTypeList<T>(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
        {
            DbConnection connection = CreateConnection();
            DbCommand command = CreateCommand(connection, sqlSegment, parameters, commandType);

            IList<T> entities = new List<T>();
            try
            {
                using (DbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        T result = default(T);
                        object value = reader[0];
                        if (value == null || value == DBNull.Value)
                            continue;
                        result = (T)TypeDescriptor.GetConverter(typeof(T)).ConvertFrom(value.ToString());
                        entities.Add(result);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("执行SQL语句发生错误:" + sqlSegment + ".详细描述" + ex.Message, ex);
            }
            finally
            {
                CloseConnection(connection);
            }
            command.Parameters.Clear();
            return entities;
        }

    }

    public enum DataBaseType
    {
        Access,
        SqlServer,
        Oracle,
        MySql
    }
}
